package com.kucun.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import org.sdlc.com.database.BaseDaoInterface;
import org.sdlc.com.database.bean.CglibDynaBean;
import org.sdlc.com.database.impl.MysqlBaseDaoImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.google.gson.JsonArray;
import com.kucun.bean.Billdetail;
import com.kucun.bean.Goods;
import com.kucun.bean.Page;
import com.kucun.bean.Repertory;
import com.kucun.bean.Users;
import com.kucun.service.GoodsService;
import com.kucun.service.RepertoryService;
import com.kucun.util.ExportExcelUtil;
import com.kucun.util.GsonUtils;
import com.kucun.util.JsonTreeData;
import com.kucun.util.JsonValueProcessorImpl;
import com.kucun.util.TreeNodeService;
import com.kucun.util.TreeNodeUtil;
import com.kucun.util.UID;
import com.times.util.DateUtils;


import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.JsonConfig;


@Controller
@RequestMapping("/report")
public class ReportController {
	private static final Logger logger = Logger.getLogger(ReportController.class);
	/**
	 * 使用记录查询页面
	 * @param request
	 * @return
	 * @throws IOException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	@RequestMapping("/reportalldate.html")
	public String getAllUser(int rows,int page,HttpServletRequest request,HttpServletResponse response) throws IOException, IllegalAccessException, InvocationTargetException{
		  String field=request.getParameter("field");
          String searchertext=request.getParameter("searchertext");
          String searcherSql="";
          if("".equals(searchertext)||searchertext==null){

          }else{
              searchertext = URLDecoder.decode(searchertext, "utf-8");
              searcherSql=" and  "+field+" like CONCAT('%','"+searchertext+"','%' ) ";
          } 
          Page page1=new Page();
          page1.setI(rows*(page-1));
          page1.setRows(rows);
          page1.setFiled(field);
          page1.setSearchertext(searchertext);  
		BaseDaoInterface basedao=MysqlBaseDaoImpl.getInstance();
	        List<CglibDynaBean> lists=null;
	        String sql="SELECT * FROM ( SELECT  t.id, t.parentid, t.clientname, t.clientid, t.assetdescription, t.goodname, t.goodid, t.goodnumber,   t.snno, t.repartoyname, t.repartoyrack, t.repartoyaddress, t.positionnumber,t.couriernumber, t.createdate,   t.remark, t.beiyon, t.goodsbillno, t.goodsgoout, t.leadingperson, t.leadingtime, t.unit,t.specifications,b.billno,b.billtype,b.states" +
	        		" FROM   billdetail t LEFT JOIN billmaster b ON t.parentid=b.id WHERE 1=1 AND t.states='已完成' AND t.billtype='入库单'  UNION SELECT t.id, t.parentid, t.clientname, t.clientid, t.assetdescription, t.goodname, t.goodid, (t.goodnumber)*-1 AS goodnumber,   t.snno, t.repartoyname, t.repartoyrack, t.repartoyaddress, t.positionnumber,t.couriernumber, t.createdate,   t.remark, " +
	        		"t.beiyon, t.goodsbillno, t.goodsgoout, t.leadingperson, t.leadingtime, t.unit,t.specifications,t.beiyon AS billno,b.billtype,b.states FROM   billdetail t LEFT JOIN billmaster b ON t.parentid=b.id WHERE 1=1 AND t.states='已完成' AND t.billtype='出库单')s1 WHERE 1=1  "+searcherSql+"  ORDER BY s1.billno DESC    LIMIT ?,? ";
	        
	        
	        String sql1="SELECT count(goodnumber) as total FROM ( SELECT  t.id, t.parentid, t.clientname, t.clientid, t.assetdescription, t.goodname, t.goodid, t.goodnumber,   t.snno, t.repartoyname, t.repartoyrack, t.repartoyaddress,t.positionnumber, t.couriernumber, t.createdate,   t.remark, t.beiyon, t.goodsbillno, t.goodsgoout, t.leadingperson, t.leadingtime, t.unit,t.specifications,b.billno,b.billtype,b.states" +
	        		" FROM   billdetail t LEFT JOIN billmaster b ON t.parentid=b.id WHERE 1=1 AND t.states='已完成' AND t.billtype='入库单'  UNION SELECT t.id, t.parentid, t.clientname, t.clientid, t.assetdescription, t.goodname, t.goodid, (t.goodnumber)*-1 AS goodnumber,   t.snno, t.repartoyname, t.repartoyrack, t.repartoyaddress,t.positionnumber, t.couriernumber, t.createdate,   t.remark, " +
	        		"t.beiyon, t.goodsbillno, t.goodsgoout, t.leadingperson, t.leadingtime, t.unit,t.specifications,b.billno,b.billtype,b.states FROM   billdetail t LEFT JOIN billmaster b ON t.parentid=b.id WHERE 1=1 AND t.states='已完成' AND t.billtype='出库单')s1 WHERE 1=1  "+searcherSql+"   ";
	        logger.info("查询sql明细分页======》"+sql);
	        logger.info("查询sql求和======》"+sql1);
	        lists= basedao.getListCglib(sql,page1.getI(),page1.getRows());
	        List<Billdetail> bilist=new ArrayList<Billdetail>();
	        Page page2=basedao.getEntityBySql(Page.class,sql1);
	        Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map  
            for (CglibDynaBean cg : lists) {
            	Billdetail bi=new Billdetail();
            	//bi.setId((Integer) cg.getValue("id"));
            	bi.setBillno((String) cg.getValue("billno"));
            	bi.setBilltype((String) cg.getValue("billtype"));
            	bi.setClientname((String) cg.getValue("clientname"));
            	bi.setGoodname((String) cg.getValue("goodname"));
            	bi.setGoodnumber((Double) cg.getValue("goodnumber"));
            	bi.setAssetdescription((String) cg.getValue("assetdescription"));
            	bi.setSnno((String) cg.getValue("snno"));
            	bi.setUnit((String) cg.getValue("unit"));
            	bi.setRepartoyname((String) cg.getValue("repartoyname"));
            	bi.setRepartoyrack((String) cg.getValue("repartoyrack"));
            	bi.setRepartoyaddress((String) cg.getValue("repartoyaddress"));
            	bi.setCouriernumber((String) cg.getValue("couriernumber"));
            	bi.setStates((String) cg.getValue("states"));
            	bi.setLeadingperson((String) cg.getValue("leadingperson"));
            	bi.setLeadingtime((Date) cg.getValue("leadingtime"));
            	bi.setGoodsbillno((String) cg.getValue("goodsbillno"));
            	bi.setGoodsgoout((String) cg.getValue("goodsgoout"));
            	bi.setSpecifications((String)cg.getValue("specifications"));
            	bi.setRemark((String)cg.getValue("remark"));
            	bi.setPositionnumber((String)cg.getValue("positionnumber"));
            	//System.out.println((String)cg.getValue("specifications"));
            	bilist.add(bi);
			}
	        jsonMap.put("total",page2.getTotal());//total键 存放总记录数，必须的  
	        jsonMap.put("rows",bilist);//rows键 存放每页记录 list  
	        PrintWriter pw = response.getWriter();
			//date类型的转化方式
		   // Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd HH:mm:ss");
		    Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd");
		    pw.write(jsonFromBean.toString());        
           	return null;
	}
	/**只统计入库单相关数据
	 * 库存盘点
	 * @param request
	 * @return
	 * @throws IOException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	@RequestMapping("/reportstocktaking.html")
	public String getAllStockTaking(int rows,int page,HttpServletRequest request,HttpServletResponse response) throws IOException, IllegalAccessException, InvocationTargetException{
		  String field=request.getParameter("field");
          String searchertext=request.getParameter("searchertext");
		  String field2=request.getParameter("field2");
          String searchertext2=request.getParameter("searchertext2");
          String searcherSql="";
          if("".equals(searchertext)||searchertext==null){

          }else{
              searchertext = URLDecoder.decode(searchertext, "utf-8");
              searcherSql=" and  "+field+" like CONCAT('%','"+searchertext+"','%' ) ";
          } 
          if("".equals(searchertext2)||searchertext2==null){

          }else{
              searchertext2 = URLDecoder.decode(searchertext2, "utf-8");
              searcherSql+=" and  "+field2+" like CONCAT('%','"+searchertext2+"','%' ) ";
          } 
          
          Page page1=new Page();
          page1.setI(rows*(page-1));
          page1.setRows(rows);
          page1.setFiled(field);
          page1.setSearchertext(searchertext);  
		    BaseDaoInterface basedao=MysqlBaseDaoImpl.getInstance();
	        List<CglibDynaBean> lists=null;
	        String sql="SELECT * FROM   billdetail t  WHERE 1=1  "+searcherSql+" AND t.states='已完成' AND t.billtype='入库单' AND t.stockrate>0   ORDER BY t.clientname DESC   LIMIT ?,? ";
	        String sql1="SELECT count(goodnumber) as total FROM billdetail t  WHERE 1=1  "+searcherSql+" AND t.states='已完成' AND t.billtype='入库单'   AND t.stockrate>0  ";
	        logger.info("查询sql分页=Rows："+rows+"= I:"+rows*(page-1)+"====》"+sql);
	        logger.info("查询sql求和======》"+sql1);
	        lists= basedao.getListCglib(sql,page1.getI(),page1.getRows());
	        List<Billdetail> bilist=new ArrayList<Billdetail>();
	        Page page2=basedao.getEntityBySql(Page.class,sql1);
	        Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map  
            for (CglibDynaBean cg : lists) {
            	Billdetail bi=new Billdetail();
            	//bi.setId((Integer) cg.getValue("id"));
            	bi.setBillno((String) cg.getValue("beiyon"));
            	bi.setBeiyon((String)cg.getValue("beiyon"));
            	bi.setBilltype((String) cg.getValue("billtype"));
            	bi.setClientname((String) cg.getValue("clientname"));
            	bi.setGoodname((String) cg.getValue("goodname"));
            	bi.setGoodnumber((Double) cg.getValue("goodnumber"));
            	bi.setAssetdescription((String) cg.getValue("assetdescription"));
            	bi.setSnno((String) cg.getValue("snno"));
            	bi.setUnit((String) cg.getValue("unit"));
            	bi.setRepartoyname((String) cg.getValue("repartoyname"));
            	bi.setRepartoyrack((String) cg.getValue("repartoyrack"));
            	bi.setRepartoyaddress((String) cg.getValue("repartoyaddress"));
            	bi.setCouriernumber((String) cg.getValue("couriernumber"));
            	bi.setStates((String) cg.getValue("states"));
            	bi.setLeadingperson((String) cg.getValue("leadingperson"));
            	bi.setLeadingtime((Date) cg.getValue("leadingtime"));
            	bi.setGoodsbillno((String) cg.getValue("goodsbillno"));
            	bi.setGoodsgoout((String) cg.getValue("goodsgoout"));
            	bi.setSpecifications((String)cg.getValue("specifications"));
            	bi.setStockrate((Double) cg.getValue("stockrate"));
            	bi.setPositionnumber((String)cg.getValue("positionnumber"));
            	bi.setRemark((String)cg.getValue("remark"));
            	//System.out.println((String)cg.getValue("specifications"));
            	bilist.add(bi);
			}
	        jsonMap.put("total",page2.getTotal());//total键 存放总记录数，必须的  
	        jsonMap.put("rows",bilist);//rows键 存放每页记录 list  
	        PrintWriter pw = response.getWriter();
			//date类型的转化方式
		   // Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd HH:mm:ss");
		    Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd");
		    pw.write(jsonFromBean.toString());        
           	return null;
	}
	@RequestMapping("/exportdate.html")
	public String toExportDate(HttpServletRequest request,HttpServletResponse response) throws Exception{
		response.reset();  
        request.setCharacterEncoding("UTF-8");  
        String hf=request.getParameter("hfs");  
       // System.out.println(hf);
        String filename=request.getParameter("filename"); 
        String type=request.getParameter("type");  
        String exportname=filename;  
            if(type.equals("excel"))  
            {  
                exportname+=".xls";  
                response.setHeader("Content-disposition", "attachment; filename="+java.net.URLEncoder.encode(exportname, "UTF-8")+"");  
                //response.setContentType("application/msexcel;charset=utf-8");  
                //response.setContentType("application/vnd.ms-excel;charset=utf-8");
                // 设置请求
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
            }  
            else if(type.equals("word"))  
            {  
                exportname+=".doc";  
                response.setHeader("Content-disposition", "attachment; filename="+java.net.URLEncoder.encode(exportname, "UTF-8")+"");  
                response.setContentType("application/ms-word;charset=UTF-8");  
            }  
          
	        PrintWriter out;  
	        out = response.getWriter();  
	        out.println(hf);  
	        out.close();  
       
		return null;
	}
	
	
	/**出库，入库
	 * 导出当前查询结果中的数据到excl中
	 * @param request
	 * @return
	 * @throws IOException 
	 */
	@SuppressWarnings("unused")
	@RequestMapping("/getalldatetoexcl.html")
	public void  GetExportToExcl1(HttpServletRequest request,HttpServletResponse response) throws IOException{
		  String field=request.getParameter("field");
          String billtype=request.getParameter("billtype");
          String searchertext=request.getParameter("searchertext");
          Users user=(Users)request.getSession().getAttribute("user");
          String searcherSql="";
          if("".equals(billtype)||billtype==null){

          }else{
        	  billtype = URLDecoder.decode(billtype, "utf-8");
          } 
          if("".equals(searchertext)||searchertext==null){

          }else{
        	  searchertext = URLDecoder.decode(searchertext, "utf-8");
        	  searcherSql=" and  "+field+" like CONCAT('%','"+searchertext+"','%' ) ";
          } 
          
	        List<Billdetail> lists=null;
	        BaseDaoInterface basedao=MysqlBaseDaoImpl.getInstance();
	        String sql="SELECT * FROM   billdetail t  WHERE 1=1   AND  t.parentid IN( SELECT b.id FROM ( SELECT id FROM billmaster t WHERE billtype='"+billtype+"' AND t.createid="+user.getUserid()+" AND t.states='草稿'  "+searcherSql+"  UNION SELECT id FROM billmaster t WHERE  billtype='"+billtype+"' AND t.states='已完成'  "+searcherSql+") b where 1=1 )  AND t.billtype='"+billtype+"'  ORDER BY t.clientname  desc ";

	        logger.info("查询sql：===》"+sql);
	        lists= basedao.getListBySql(sql, Billdetail.class, null);
	        List<Billdetail> bilist=new ArrayList<Billdetail>();
	        logger.info(user.getUsername() +" date:"+DateUtils.getStringDateShort()+"导出【"+billtype+"】"+JSONArray.fromObject(lists).toString());
		    String excelHeader=""; 
		    if("入库单".equals(billtype)){
		    	excelHeader="客户姓名#clientname,物品名称#goodname,规格型号#specifications,库存编号#beiyon,物品数量#goodnumber,物品单位#unit,SN#snno," +
				    	//	"仓库名称#repartoyname,货架#repartoyrack,货架位置#repartoyaddress,快递单号#couriernumber,物品去向#goodsgoout,单据类型#states" ;
				    	"仓库名称#repartoyname,货架#repartoyrack,货架位置#repartoyaddress,操作人#leadingperson,操作时间#leadingtime,位置编号#positionnumber,备注#remark" ;
				    		//+",完成时间#createdate";  
		    }else{
		    	excelHeader="客户姓名#clientname,物品名称#goodname,规格型号#specifications,库存编号#beiyon,物品数量#goodnumber,物品单位#unit,SN#snno," +
				    	//	"仓库名称#repartoyname,货架#repartoyrack,货架位置#repartoyaddress,快递单号#couriernumber,物品去向#goodsgoout,单据类型#states" ;
				    	"仓库名称#repartoyname,货架#repartoyrack,货架位置#repartoyaddress,操作人#leadingperson,操作时间#leadingtime,位置编号#positionnumber,物品去向#goodsgoout" ;
				    		//+",完成时间#createdate";     
		    }
		    String totle="goodnumber";  
		    try {
				ExportExcelUtil.export(request,response, billtype, excelHeader, lists, null);
			} catch (Exception e) {
				e.printStackTrace();
			}
	}
	@SuppressWarnings("unused")
	@RequestMapping("/getdown.html")
	public void  Getdown(HttpServletRequest request,HttpServletResponse response) throws IOException{
	response.setContentType("text/html");  
	javax.servlet.ServletOutputStream ou = response.getOutputStream();  
	//String filepath=(String)request.getAttribute("path");  
	String filepath="d://html5.zip";  
	File file = new File(filepath);  
	if (!file.exists()) {  
	System.out.println(file.getAbsolutePath() + " 文件不存在!");  
	return;  
	}    
	FileInputStream fileInputStream = new FileInputStream(file);  	  
	if (filepath != null && filepath.length() > 0) {  
	response.setContentType("application/x-msdownload");  
	response.setHeader("Content-Disposition", "attachment; filename=" + new String(file.getName().getBytes("gb2312"),"iso8859-1") + "");  
	if (fileInputStream != null) {  
	int filelen = fileInputStream.available();  
	byte a[] = new byte[filelen];  
	fileInputStream.read(a);  
	ou.write(a);  
	}  
	ou.close();
	}
	}
	
	
	
	
	/**只统计入库单相关数据
	 * 库存盘点
	 * @param request
	 * @return
	 * @throws IOException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	@RequestMapping("/reportstockSNtaking.html")
	public String getAllSNStockTaking(int rows,int page,HttpServletRequest request,HttpServletResponse response) throws IOException, IllegalAccessException, InvocationTargetException{
		  String field=request.getParameter("field");
		
          String searchertext=request.getParameter("searchertext");
		  String field2=request.getParameter("field2");
          String searchertext2=request.getParameter("searchertext2");
          String searcherSql="";
          
        
          
          if("".equals(searchertext)||searchertext==null){

          }else{
              searchertext = URLDecoder.decode(searchertext, "utf-8");
              searcherSql=" and  "+field+" like CONCAT('%','"+searchertext+"','%' ) ";
          } 
          if("".equals(searchertext2)||searchertext2==null){

          }else{
              searchertext2 = URLDecoder.decode(searchertext2, "utf-8");
              searcherSql+=" and  "+field2+" like CONCAT('%','"+searchertext2+"','%' ) ";
          } 
          
          Page page1=new Page();
          page1.setI(rows*(page-1));
          page1.setRows(rows);
          page1.setFiled(field);
          page1.setSearchertext(searchertext);  
		    BaseDaoInterface basedao=MysqlBaseDaoImpl.getInstance();
	        List<CglibDynaBean> lists=null;
	        String sql="select * from ( select sn.sn as snno,if(sn.states=0,'入库','出库') as billtype,b.clientname,b.goodname,b.unit,b.repartoyname,b.repartoyrack,b.repartoyaddress,b.positionnumber,b.beiyon,b.specifications,b.stockrate,b.remark from sntab sn LEFT join  billdetail b on sn.indtlid=b.id) t   WHERE 1=1  "+searcherSql+"    ORDER BY t.clientname DESC   LIMIT ?,? ";
	        String sql1="SELECT count(1) as total FROM (select * from ( select sn.sn as snno,if(sn.states=0,'入库','出库') as billtype,b.clientname,b.goodname,b.unit,b.repartoyname,b.repartoyrack,b.repartoyaddress,b.positionnumber,b.beiyon,b.specifications,b.stockrate,b.remark from sntab sn LEFT join  billdetail b on sn.indtlid=b.id) s  ) t  WHERE 1=1  "+searcherSql+" ";
	        logger.info("查询sql分页=Rows："+rows+"= I:"+rows*(page-1)+"====》"+sql);
	        logger.info("查询sql求和======》"+sql1);
	        lists= basedao.getListCglib(sql,page1.getI(),page1.getRows());
	        List<Billdetail> bilist=new ArrayList<Billdetail>();
	        Page page2=basedao.getEntityBySql(Page.class,sql1);
	        Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map  
            for (CglibDynaBean cg : lists) {
            	Billdetail bi=new Billdetail();
            	//bi.setId((Integer) cg.getValue("id"));
            	bi.setBillno((String) cg.getValue("beiyon"));
            	bi.setBeiyon((String)cg.getValue("beiyon"));
            	bi.setBilltype((String) cg.getValue("billtype"));
            	bi.setClientname((String) cg.getValue("clientname"));
            	bi.setGoodname((String) cg.getValue("goodname"));
            	bi.setGoodnumber((Double) cg.getValue("goodnumber"));
            	//bi.setAssetdescription((String) cg.getValue("assetdescription"));
            	bi.setSnno((String) cg.getValue("snno"));
            	bi.setUnit((String) cg.getValue("unit"));
            	bi.setRepartoyname((String) cg.getValue("repartoyname"));
            	bi.setRepartoyrack((String) cg.getValue("repartoyrack"));
            	bi.setRepartoyaddress((String) cg.getValue("repartoyaddress"));
            	//bi.setCouriernumber((String) cg.getValue("couriernumber"));
            	
            	
            	//bi.setLeadingperson((String) cg.getValue("leadingperson"));
            	//bi.setLeadingtime((Date) cg.getValue("leadingtime"));
            	bi.setGoodsbillno((String) cg.getValue("goodsbillno"));
            	//bi.setGoodsgoout((String) cg.getValue("goodsgoout"));
            	bi.setSpecifications((String)cg.getValue("specifications"));
            	bi.setStockrate(1.0);
            	bi.setPositionnumber((String)cg.getValue("positionnumber"));
            	bi.setRemark((String)cg.getValue("remark"));
            	//System.out.println((String)cg.getValue("specifications"));
            	bilist.add(bi);
			}
	        jsonMap.put("total",page2.getTotal());//total键 存放总记录数，必须的  
	        jsonMap.put("rows",bilist);//rows键 存放每页记录 list  
	        PrintWriter pw = response.getWriter();
			//date类型的转化方式
		   // Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd HH:mm:ss");
		    Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd");
		    pw.write(jsonFromBean.toString());        
           	return null;
	}
}
